package Java100_1006;

import Java100_1006.Book;
import Java100_1006.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//封装 对Book表的增删查改操作
public class BookDao {

    //1.新增书籍（插入）
    public boolean add(Book book){
        //把book对象插入数据库的书籍表中
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();

            //2.拼装sql语句
            String sql = "insert into book values(null,?,?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,book.getName());
            statement.setString(2,book.getAuthor());
            statement.setInt(3,book.getPrice());
            statement.setString(4,book.getType());
            statement.setInt(5,book.isBorrowed()?1:0);

            //3.执行SQL
            int ret = statement.executeUpdate();
            if(ret == 1)
                return true;
            return false;

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }
    //2.查找所有书籍（查找）
    public List<Book> selectAll() {
        List<Book> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            //1.与数据库建立连接
            connection = DBUtil.getConnection();
            //2.拼装SQL
            String sql = "select * from book";
            statement = connection.prepareStatement(sql);
            //3.执行SQL
            resultSet = statement.executeQuery();
            //4.遍历结果集合
            while(resultSet.next()){
                //取出当前行的这些列，构造一个Book对象来
                Book book = new Book();
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setBorrowed(resultSet.getInt("isBorrowed")==1);
                //把这个book对象添加到链表中
                list.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return list;
    }
    //3.根据名字查找书籍（查找）
    public List<Book> selectByName(String name){
        List<Book> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            //1.先与数据库建立连接
            connection = DBUtil.getConnection();
            //2.拼装SQL语句
            String sql = "select * from book where name=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,name);
            //3.执行SQL
            resultSet = statement.executeQuery();
            //4.遍历结果集合
            while(resultSet.next()){
                Book book = new Book();
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setBorrowed(resultSet.getInt("isBorrowed") == 1);
                list.add(book);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return list;
    }
    //4.删除书籍（删除）
    public boolean delete(int bookId){
     Connection connection = null;
     PreparedStatement statement = null;
        try {
            //1.建立连接
            connection = DBUtil.getConnection();
            //2.拼装SQL
            String sql = "delete from book where bookId=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);
            //3.执行SQL
            int ret = statement.executeUpdate();
            if(ret != 1){
                return  false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }
    //5.借书（修改）
    public boolean borrowBook(int bookId){

        Connection connection = null;
        //statement1用于查找操作
        PreparedStatement statement1 = null;
        //statement2用于修改操作
        PreparedStatement statement2 = null;
        ResultSet resultSet = null;
        try {
            //1.建立连接
            connection = DBUtil.getConnection();
            //要借书，就必须知道这本书存不存在，所以我们先查找，再操作
            //2.拼装SQL
            String sql = "select * from book where bookId=?";
            statement1 = connection.prepareStatement(sql);
            statement1.setInt(1,bookId);
            //3.执行SQL
            resultSet = statement1.executeQuery();
            //4.遍历结果，预期结果要么是1条，要么是0条
            if(resultSet.next()){
                //进入if就说明书是存在的
                boolean isBorrowed = (resultSet.getInt("isBorrowed")==1);
                if(isBorrowed) {
                    //说明书已经被借出了，不能再次借阅
                    System.out.println("书已经借出! 无法再次借出! bookId = " + bookId);
                    return false;
                }
            }else{
                //进入else，那么书是不存在的
                System.out.println("书不存在! 无法借出! bookId = " + bookId);
                return false;
            }
            //[接下来是开始进行借书逻辑]
            //5.拼装SQL
            sql = "update book set isBorrowed=1 where bookId=?";
            statement2 = connection.prepareStatement(sql);
            statement2.setInt(1,bookId);

            //6.执行SQL
            int ret = statement2.executeUpdate();
            if(ret != 1){
                return false;
            }
            return true;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement2 != null){
                try {
                    statement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement1 != null){
                try {
                    statement1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
    //6.还书（修改）
    public boolean returnBook(int bookId){
        Connection connection = null;
        PreparedStatement statement1 = null;
        PreparedStatement statement2 = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();
            //查询书是否存在
            String sql = "select * from book where bookId=?";
            statement1 = connection.prepareStatement(sql);
            statement1.setInt(1,bookId);
            resultSet = statement1.executeQuery();
            if(resultSet.next()){
                //进入if语句就说明这本书存在于书籍名单中
                boolean isBorrowed = (resultSet.getInt("isBorrowed")==1);
                if(!isBorrowed){
                    System.out.println("书没有借出, 没必要还! bookId = " + bookId);
                    return false;
                }
            }else{
                //不是图书系统中的书，不需要还
                System.out.println("书不存在, 无法归还! bookId = " + bookId);
                return false;
            }
            //[还书]
            //修改书籍的借出状态就可
            sql = "update book set isBorrowed=0 where bookId=?";
            statement2 = connection.prepareStatement(sql);
            statement2.setInt(1,bookId);
            int ret = statement2.executeUpdate();
            if(ret != 1){
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement2 != null){
                try {
                    statement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement1 != null){
                try {
                    statement1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
}
